﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.ComponentModel;
using SQX.DataModel;
using System.Collections.ObjectModel;
using Microsoft.AnalysisServices.AdomdClient;
using System.Data;
using System.Data.SqlClient;

namespace SQX.ViewModel
{
    public class ConnectionViewModel : ViewModelBasic
    {
        private string connectionString = "Data Source={0};Integrated Security=SSPI;Persist Security Info=True;";
        private string servername = "";
        private string catalogname = "";
        private string dbname = "";
        private Service.SourceType connectionType;
        private bool actionResult = false;
        private AdomdConnection con;
        private SqlConnection sqlcon;
        private string connectionStatus = "Ready to connect";




        public ObservableCollection<string> Databases
        {
            get
            {
                try
                {
                    switch (this.connectionType)
                    {
                        case Service.SourceType.SourceOLAP:
                            if (this.ServerName != "")
                            {
                                ObservableCollection<string> result = new ObservableCollection<string>();
                                con.ConnectionString = string.Format(this.connectionString, this.ServerName);
                                con.Open();
                                AdomdCommand cmd = new AdomdCommand(@"select * from $system.DBSCHEMA_CATALOGS", con);
                                AdomdDataReader rdr = cmd.ExecuteReader();
                                foreach (object x in rdr.Cast<IDataRecord>())
                                {
                                    result.Add((x as IDataRecord)[0].ToString());
                                }
                                rdr.Close();
                                con.Close();
                                return result;
                            }
                            else return null;
                        case Service.SourceType.SourceSQL:
                            if (this.ServerName != "")
                            {
                                ObservableCollection<string> result = new ObservableCollection<string>();
                                sqlcon.ConnectionString = string.Format(this.connectionString, this.ServerName);
                                sqlcon.Open();
                                var databases = sqlcon.GetSchema("Databases");
                                sqlcon.Close();
                                foreach (DataRow rw in databases.Rows)
                                {
                                    result.Add(rw["database_name"].ToString());
                                }
                                return result;
                            }
                            else return null;
                        default:
                            return null;
                    }
                }
                catch (Exception ex)
                {
                    this.ConnectionStatus = "Error: " + ex.Message;
                    return null;
                }
            }
        }

        public string ConnectionStatus
        {
            get
            {
                return connectionStatus;
            }
            set
            {
                connectionStatus = value;
                OnPropertyChanged("ConnectionStatus");
            }
        }

        public ObservableCollection<string> Cubes
        {
            get
            {
                try
                {
                    if (this.connectionType == Service.SourceType.SourceOLAP)
                    {
                        if (this.ServerName != "" && this.DatabaseName != "")
                        {
                            ObservableCollection<string> result = new ObservableCollection<string>();
                            con.ConnectionString = (string.Format(this.connectionString, this.ServerName) + "Initial Catalog=" + this.DatabaseName + ";");
                            con.Open();
                            AdomdCommand cmd = new AdomdCommand(@"select [CUBE_NAME] from $system.MDSCHEMA_CUBES where instr([CUBE_NAME],'$') = 0 and len([BASE_CUBE_NAME])=0", con);
                            AdomdDataReader rdr = cmd.ExecuteReader();
                            foreach (object x in rdr.Cast<IDataRecord>())
                            {
                                result.Add((x as IDataRecord)[0].ToString());
                            }
                            rdr.Close();
                            con.Close();
                            return result;
                        }
                        else return null;
                    }
                    else return null;
                }
                catch (Exception ex)
                {
                    connectionStatus = ex.Message;
                    return null;
                }
            }
        }

        public ConnectionViewModel(Service.SourceType ConnectionType)
        {
            try
            {
                this.connectionType = ConnectionType;
                switch (ConnectionType)
                {
                    case Service.SourceType.SourceOLAP:
                        this.servername = Properties.Settings.Default.LastOLAPServer;
                        this.dbname = Properties.Settings.Default.LastOLAPDB;
                        this.catalogname = Properties.Settings.Default.LastOLAPCube;
                        this.con = new AdomdConnection();
                        break;
                    case Service.SourceType.SourceSQL:
                        this.servername = Properties.Settings.Default.LastSQLServer;
                        this.dbname = Properties.Settings.Default.LastSQLDB;
                        this.sqlcon = new SqlConnection();
                        break;
                }
            }
            catch (Exception ex)
            {
                // TODO: change to status bar
                this.servername = ex.Message;
            }
        }

        public string ServerName
        {
            get { return servername; }
            set 
            { 
                servername = value;
                switch (this.connectionType)
                {
                    case Service.SourceType.SourceOLAP:
                            Properties.Settings.Default.LastOLAPServer = servername;
                        break;
                    case Service.SourceType.SourceSQL:
                            Properties.Settings.Default.LastSQLServer = servername;
                        break;
                }
                Properties.Settings.Default.Save();
                OnPropertyChanged("ServerName"); OnPropertyChanged("Databases"); 
            }
        }

        public string CatalogName
        {
            get { return catalogname; }
            set 
            { 
                catalogname = value;
                Properties.Settings.Default.LastOLAPCube = catalogname;
                Properties.Settings.Default.Save();
                OnPropertyChanged("CatalogName"); 
            }
        }

        public string DatabaseName
        {
            get { return dbname; }
            set 
            { 
                dbname = value;
                switch (this.connectionType)
                {
                    case Service.SourceType.SourceOLAP:
                            Properties.Settings.Default.LastOLAPDB = dbname;
                        break;
                    case Service.SourceType.SourceSQL:
                            Properties.Settings.Default.LastSQLDB = dbname;
                        break;
                }
                Properties.Settings.Default.Save();
                OnPropertyChanged("DatabaseName"); OnPropertyChanged("Cubes"); 
            }
        }

        public string Caption
        {
            get
            {
                return "Connecting to " + Service.GetDescription(this.connectionType);
            }
        }

        public bool ActionResult
        {
            get { return actionResult; }
            set { actionResult = value; }
        }

        public bool CubeComboEnabled
        {
            get { return this.connectionType == Service.SourceType.SourceOLAP; }
        }

        public object ConnectionObject
        {
            get
            {
                switch (this.connectionType)
                {
                    case Service.SourceType.SourceOLAP:
                        if (con.State != ConnectionState.Open) con.Open();
                        return con;
                    case Service.SourceType.SourceSQL:
                        string connectionString = "Data Source={0};Initial Catalog={1};Integrated Security=SSPI;Persist Security Info=True;";
                        var newSqlCon = new SqlConnection(string.Format(connectionString, this.ServerName,this.DatabaseName));
                        newSqlCon.Open();
                        return newSqlCon;
                    default:
                        return null;
                }
            }
        }
        
    }
}
